using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using YDTH.ydthDataSetTableAdapters;

namespace YDTH
{
    class ExcelFun
    {
        public static DataTable XlsToDataTable(String strpath)
        {
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + strpath + ";" +
                "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
            DataTable dt = new DataTable();
            myCommand.Fill(dt);
            return dt;
        }
        public static String getInsureId(String name, String idNo)
        {
            AppInfoTableAdapter appInfoTableAdapter = new AppInfoTableAdapter();
            System.Nullable<int> insuredno = appInfoTableAdapter.QueryInsuredNo(name, idNo);
            if (insuredno == null)
            {
                return null;
            }
            return ""+insuredno;
        }
        public static String getNowClaim()
        {
            batchTableAdapter tbatchTableAdapter = new batchTableAdapter();
            ydthDataSet.batchDataTable data = tbatchTableAdapter.GetNowClaim();
            if (data.Rows.Count < 1)
            {
                return null;
            }
            else
            {
                return data.Rows[0][0].ToString();
            }
        }
        public static void createExcel(String fileName, String tableName, String[] cols, DataRowCollection table)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + fileName + ";" +
                "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            OleDbCommand objCmd = new OleDbCommand();
            objCmd.Connection = conn;
            objCmd.CommandText = @"create table " + tableName + " (";
            for (int i = 0; i < cols.Length;i++ )
            {
                String col = cols[i];
                objCmd.CommandText += col + " varchar ";
                if (i != cols.Length - 1)
                {
                    objCmd.CommandText += ",";
                }
            }
            objCmd.CommandText+=")";
            objCmd.ExecuteNonQuery();
            String cmd = "insert into " + tableName + "" ;
            String field="";
            String values="";
            for (int i = 0; i < cols.Length; i++)
            {
                String col = cols[i];
                field += col;
                values += "@col"+i;
                if (i != cols.Length - 1)
                {
                    field += ",";
                    values += ",";
                }
            }
            objCmd.CommandText = cmd + " (" + field + ") values (" + values + ")";

            for (int i = 0; i < cols.Length; i++)
            {
                objCmd.Parameters.Add(new OleDbParameter("@col"+i,OleDbType.VarChar));
            }

            foreach (DataRow row in table)
            {
                for (int i = 0; i < cols.Length; i++)
                {
                    objCmd.Parameters[i].Value = row[i];
                }
                objCmd.ExecuteNonQuery();
            }
            conn.Close();
        }
    }
}
